package dbconnection;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;

import utilities.Data;

import loja.JaExisteException;
import loja.Quebra;
import loja.Stock;

/**
 * @author IME 2010
 * 
 * Class used to establish a connection with a defined DataBase
 */

public class BDquebra {
	private BaseDados base_dados = null;

	public BDquebra(BaseDados bd){
		this.base_dados=bd;
	}

	/***********************************************************
	 * Inserts the Quebra q into the DataBase
	 * @param q Quebra to be inserted in the DataBase
	 * @throws JaExisteException if the Quebra q already exists in the DataBase
	 * @return true if the Quebra q was successfully inserted into the DataBase; false otherwise
	 ***********************************************************/
	protected boolean adiciona(Quebra q){
		if(q!=null){
			String tablename="quebra";

			String query = "insert INTO `" + tablename 
			+"` (`stock_idstock`, `stock_produto_idproduto`, `stock_armazem_idarmazem`, `data`, `descricao`, `quantidade`) VALUES ('"
			+q.getStock().getIDStock()+"', '"+q.getStock().getProduto().getCodigo()+"', '"
			+q.getStock().getArmazem().getId_armazem()+"', '"
			+q.getData_quebra()+"', '"+q.getDescricao()+"', '"+q.getQuantidade()+"');" ;

			try {
				base_dados.executeStatement(query);
				return true;
			} catch (SQLException e) {
				//			if(e.getErrorCode()==1062)
				//				throw new JaExisteException(q.getIdQuebra(), getQuebra(q.getIdQuebra()));
			}
		}
		return false;
	}

	/**
	 * Updates the Quebra quebra in the DataBase to match the given fields
	 * @param quebra Quebra to be updated in the DataBase
	 * @param s	new Stock for the Quebra quebra
	 * @param data new data for the Quebra quebra
	 * @param descricao new descricao for the Quebra quebra
	 * @return true if the Quebra quebra was successfully updated; false otherwise
	 */
	protected boolean alteraQuebra(Quebra quebra, Stock s, Data data, String descricao){
		boolean sucesso = false;
		if(quebra!=null&&s!=null&&data!= null&&descricao!= null){
			String tabela = "quebra";
			String query = "UPDATE "+tabela+" SET `stock_idstock`='"+s.getIDStock()+"', `data`='"+DataToDate(data)
			+"', `descricao`='"+descricao+"' WHERE `idquebra`='"+quebra.getIdQuebra()+"';";

			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso=false;
			}
		}
		return sucesso;
	}

	protected boolean existe(Quebra q) {
		if(q!=null){
			try{
				return existe(q.getIdQuebra());
			}catch(java.lang.NumberFormatException e){
				return false;
			}
		}
		return false;
	}

	/***********************************************************
	 * Removes the Quebra q from the DataBase
	 * @param q the Quebra to be removed from the Database
	 * @return true if the Quebra q was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Quebra q)
	{
		boolean sucesso = false;
		if(q!=null&&existe(q)){


			String tablename="quebra";
			String query = "DELETE FROM " + tablename +" WHERE `idquebra` = '"+q.getIdQuebra()+"' ;";
			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				//	e.printStackTrace();
				sucesso = false;
			}
		}
		return sucesso;
	}

	/***********************************************************
	 * Removes the Quebra from the DataBase
	 * @param s the Stock of all Quebra to be removed from the Database
	 * @return true if the Quebra was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Stock s)
	{
		boolean sucesso = false;
		if(s!=null&&existe(s)){

			String tablename="quebra";
			String query = "DELETE FROM " + tablename +" WHERE `stock_idstock` = '"+s.getIDStock()+"' ;";

			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso = false;
			}
		}
		return sucesso;
	}

	protected Quebra getQuebra(int idQuebra) {
		if(existe(idQuebra)){
			Quebra q = null;
			String tablename="quebra";
			String query = "SELECT * FROM  "+tablename+"  WHERE `idquebra` = '"+idQuebra+"' ;";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				q=new Quebra(null,rs.getString(6),rs.getInt(1),rs.getString(5),rs.getInt(7));
	//			q.setStock(base_dados.getStock(rs.getInt(2)));
				q.setStock(base_dados.getStock(null));

			} catch (SQLException e) {
				e.printStackTrace();
			}
			return q;
		}
		return null;
	}

	protected LinkedList<Quebra> getQuebras(){

		if(naoVazia()){		
			LinkedList<Quebra> lista_quebras = null;
			String tabela="quebra";
			String query = "SELECT * FROM " + tabela +";";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_quebras = getQuebras(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return lista_quebras;
		}
		return null;
	}

	/***********************************************************
	 * Returns all Quebras present in the DataBase whose stock equal the s argument
	 * @param s the reference Stock with which to compare the name of each Quebras in the DataBase
	 * @return the LinkedList<Quebra> which contains all Quebras in the DataBase; null if the DataBase has no Quebras;
	 ***********************************************************/
	protected LinkedList<Quebra> getQuebras(Stock s){

		if(naoVazia() && s!=null){		
			LinkedList<Quebra> lista_quebras = null;
			String tabela="quebra";
			String coluna = "stock_idstock";
			String query = "SELECT * FROM " + tabela +" WHERE `"+coluna+"`='"+s.getIDStock()+"';";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_quebras = getQuebras(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return lista_quebras;
		}
		return null;
	}


	private LinkedList<Quebra> getQuebras(ResultSet rs) throws SQLException {
		LinkedList<Quebra> lista_quebras= new LinkedList<Quebra>();
		while (rs.next()){
//			lista_quebras.add(getQuebra(rs.getInt(1)));
//			rs.next();
			Quebra q=new Quebra(null,rs.getString(6),rs.getInt(1),rs.getString(5),rs.getInt(7));
//			q.setStock(base_dados.getStock(rs.getInt(2)));
			q.setStock(base_dados.getStock(null));

			lista_quebras.add(q);
		}
		return lista_quebras;
	}

	private boolean naoVazia() {
		String tabela = "quebra";
		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" + tabela +";";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return existe;
	}

	private boolean existe(Stock s) {
		String tablename = "quebra";
		String column_name = "stock_idstock";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+s.getIDStock()+"' ;";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return existe;
	}


	private boolean existe(int idQuebra) {
		String tablename = "quebra";
		String column_name = "idquebra";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+idQuebra+"' ;";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return existe;
	}

	private Data DateToData(Date d){
		return new Data(d.getDate(), (d.getMonth()+1), (d.getYear())+1900);
	}

	private Date DataToDate(Data d){
		return new Date(d.getAno(),d.getMes(),d.getDia());
	}

}
